﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Dapper;
using NPOI.SS.UserModel;
using System.Configuration;
using NPOI.HSSF.UserModel;

namespace WebApplication1.Controllers
{
    public class HomeController : Controller
    {
        // GET: Home
        public ActionResult Index()
        {
            //var ad = SelectColumnCats();
            //List<string> list = new List<string>();
            //string[] title = { "列名", "长度", "类型", "说明", "是否为空" };
            //foreach (var itme in ad)
            //{
            //    //list.Add(itme.name);  
            //    IEnumerable<Column> dd = SelectColumn(itme.name);

            //    Excel(title, itme.name, dd);
            //}
            Excel();
            return View();
        }
        public static void Excel()
        {
            HSSFWorkbook book = new HSSFWorkbook();
            book = ddd(book);
            MemoryStream ms = new MemoryStream();
            book.Write(ms);
            System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode("213" + "" + DateTime.Now.ToString("yyyMMddHHmmss"), System.Text.Encoding.UTF8)));
            System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray());
            System.Web.HttpContext.Current.ApplicationInstance.CompleteRequest();
            book = null;
            ms.Close();
            ms.Dispose();
        }
        public static IEnumerable<Table> SelectColumnCats()
        {
            using (IDbConnection conn = OpenConnection())
            {
                const string query = "SELECT name, id From sysobjects WHERE xtype = 'u' ORDER BY name ASC";
                return conn.Query<Table>(query, null);
            }
        }

        public static HSSFWorkbook ddd(HSSFWorkbook book)
        {
            var ad = SelectColumnCats();
            List<string> list = new List<string>();
            string[] title = { "列名", "长度", "类型", "说明", "是否为空" };
            foreach (var itme in ad)
            {
                IEnumerable<Column> dd = SelectColumn(itme.name);

                ISheet sheet = book.CreateSheet(itme.name);
                IRow headerrow = sheet.CreateRow(0);
                ICellStyle style = book.CreateCellStyle();
                style.Alignment = HorizontalAlignment.Center;
                style.VerticalAlignment = VerticalAlignment.Center;
                headerrow.CreateCell(0).SetCellValue("表名");
                headerrow.CreateCell(1).SetCellValue(itme.name);
                headerrow = sheet.CreateRow(1);
                int i = 0;

                foreach (var column in title)
                {
                    headerrow.CreateCell(i).SetCellValue(column);
                    i++;
                }
                int rowIndex = 2;

                foreach (var column in dd)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    dataRow.CreateCell(0).SetCellValue(column.name);
                    dataRow.CreateCell(1).SetCellValue(column.length);
                    dataRow.CreateCell(2).SetCellValue(column.typename);
                    dataRow.CreateCell(3).SetCellValue(column.isnullable);
                    dataRow.CreateCell(4).SetCellValue(column.tname);
                    rowIndex++;
                }
            }
            return book;

        }

        public static IEnumerable<Column> SelectColumn(string name)
        {
            using (IDbConnection conn = OpenConnection())
            {
                string query = @"SELECT syscolumns.name, syscolumns.length, systypes.name   as   typename, sys.extended_properties.[value] AS tname,syscolumns.isnullable  from   syscolumns join systypes   on     syscolumns.xtype=systypes.xtype   and   systypes.name <> 'sysname ' LEFT OUTER JOIN sys.extended_properties ON ( sys.extended_properties.minor_id = syscolumns.colid AND sys.extended_properties.major_id = syscolumns.id) where   syscolumns.id   in (select  id   from   sysobjects   where   name= @name)";
                var dd = conn.Query<Column>(query, new { name = name });
                return dd;
            }
            return null;
        }
        public static SqlConnection OpenConnection()
        {
            var d = ConfigurationManager.ConnectionStrings["AnZhen_DBContext"].ConnectionString;
            SqlConnection connection = new SqlConnection(d);
            connection.Open();
            return connection;
        }
    }
    public class Table
    {
        public string name { get; set; }
    }

    public class Column
    {
        public string name { get; set; }
        public string length { get; set; }
        public string typename { get; set; }
        public string tname { get; set; }
        public string isnullable { get; set; }

    }
}